
USE [ppjdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspGetUsersStoreList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspGetUsersStoreList]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Returns list of stores from dbo.tblStore table base 
	on value of @biUserID parameter
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	08/13/2012
  -----------------------------------------------------------------------
  Description/Purpose:

  Output values:
	@iRetCode =  0, sucess,
	@iRetCode = -1, SQL update error, detail info recorded into dbo.tblSQLAudit table
	@iRetCode = -2, Parameter @biUserID is NULL or zero
	@iRetCode = -3, User {0} not found in dbo.tblUser table
	@iRetCode = -4, Store {0} not found in dbo.tblStore table	

  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 08/13/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------

	declare
		@biUserID [bigint]
		,@iRetCode [int]
		,@szMsgTier1 [nvarchar] (2048)
		,@szMsgTier2 [nvarchar] (1024)

	select @biUserID = 1

	exec [svc].[uspGetUsersStoreList]
		@biUserID
		,@iRetCode output
		,@szMsgTier1 output   
		,@szMsgTier2 output  

	select 	
		@iRetCode as [RetCode] 
		,@szMsgTier1 as [RetMsg1]   
		,@szMsgTier2 as [RetMsg2]   		

*/

CREATE PROCEDURE [svc].[uspGetUsersStoreList]
	@biUserID [bigint]
    ,@iRetCode [int] output
    ,@szMsgTier1 [nvarchar] (2048) output   
    ,@szMsgTier2 [nvarchar] (1024) output  
    ,@bHideRevoked [bit] = 0    
AS
BEGIN

SET NOCOUNT ON;

DECLARE @iIsProcessComplete int
, @szProcessName VARCHAR(50)
, @ErrorMessage nvarchar(2048) 
, @ErrorNumber int
, @ErrorSeverity int
, @ErrorState int
, @ErrorProcedure nvarchar(2048)
, @ErrorLine int
, @TranCounter int
, @RowCount int
, @Msg nvarchar(4000)
, @iJobSetID int
, @szInsertedBy varchar(16)
, @szErrSubst [varchar] (100)

-- Get current stored proc name
SELECT @szProcessName = ISNULL(OBJECT_NAME(@@PROCID), 'uspGetUsersStoreList')

BEGIN TRY
-----------------------------------
--BEGIN WORK
-----------------------------------

select @iRetCode = 0
	,@szMsgTier1 =''
	,@szMsgTier2=''
	,@szErrSubst = ''
	,@bHideRevoked = coalesce(@bHideRevoked,0)	
	
declare @tbl table (StoreID [bigint] not null primary key,
	eCommerceType [int] not null,
	StoreName [nvarchar] (255) not null,
	RevokeStatus [bit] not null,
	DateRevoked [datetime] null,
	StoreNode [hierarchyid] null,
	ParentStoreID [bigint] null)

declare @CurrentStoreNode [hierarchyid]
	,@biStoreID [bigint] 

-- Validate parameters
if coalesce(@biUserID,0)=0
-- Parameter is NULL or zero
	set @iRetCode = -2
else 	
 begin
	select @biStoreID = StoreID from dbo.tblUser (nolock) where UserID = @biUserID
	
	if @@ROWCOUNT = 0 
		select @iRetCode = -3
			,@szErrSubst = CAST(@biUserID as [varchar] (30))
	else 
	 begin

		select @CurrentStoreNode = StoreNode 
			from dbo.tblStore (nolock) 
			where StoreID = @biStoreID

		if @@ROWCOUNT = 0 
			select @iRetCode = -4
				,@szErrSubst = CAST(@biStoreID as [varchar] (30))
	 end
 end

if @iRetCode = 0
 begin

	insert into @tbl (
		StoreID,
		eCommerceType,
		StoreName,
		RevokeStatus,
		DateRevoked,
		StoreNode,
		ParentStoreID)
	select 
		StoreID,
		eCommerceType,
		StoreName,
		RevokeStatus,
		DateRevoked,
		StoreNode,
		ParentStoreID
		from dbo.tblStore (nolock) 
		where StoreNode.IsDescendantOf(@CurrentStoreNode) = 1 

	if @bHideRevoked = 1
	 begin
		delete from @tbl where RevokeStatus = 1
	 end
	
	select t.StoreID,
		t.eCommerceType,
		vw.LookUpShortName as eCommerceName,
		t.StoreName,
		t.RevokeStatus,
		t.DateRevoked,
		t.StoreNode.ToString() as RNumber,
		t.ParentStoreID
	from @tbl t 
	left join dbo.vwLookUp vw (nolock) on vw.LookUpName = 'eCommerceType'
		and vw.LookUpSubType = t.eCommerceType
	order by RNumber	
 end
else
 begin
	select @szMsgTier1=replace(t.MsgTier1,'{0}',''''+@szErrSubst+''''),
		@szMsgTier2=replace(u.MsgTier2,'{0}',''''+@szErrSubst+''''),
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode
 end

 RETURN 0
END TRY
BEGIN CATCH
-- If failed rollback
-- get extended error information
	select @ErrorNumber  = ERROR_NUMBER(), @ErrorSeverity  = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
	, @ErrorProcedure  = ERROR_PROCEDURE(), @ErrorLine  = ERROR_LINE(), @ErrorMessage  = ERROR_MESSAGE()
	set @Msg = '; ErrorNumber = ' + cast(@ErrorNumber as varchar(4))
	+ ', ErrorSeverity  = '       + cast(@ErrorSeverity as varchar(4))
	+ ', ErrorState = '                 + cast(@ErrorState as varchar(4))
	+ ', ErrorProcedure = '       + cast(@ErrorProcedure as varchar(128))
	+ ', ErrorLine = '                  + cast(@ErrorLine as varchar(4))
	+ ', ErrorMessage = '         + cast(@ErrorMessage as varchar(2048))

	IF @TranCounter is not null
	 begin
		IF @TranCounter = 0 -- Transaction started in procedure.
		 ROLLBACK TRANSACTION;
		ELSE IF XACT_STATE() <> -1 -- roll back to the savepoint
		 ROLLBACK TRANSACTION uspGetUsersStoreList;
	 end

	-- log error
	EXECUTE  dbo.uspRecordSQLAudit
	@biProcessID = -1
	,@iErrorNumber = @ErrorNumber
	,@iErrorSeverity = @ErrorSeverity
	,@iErrorState = @ErrorState
	,@szErrorProcedure = @ErrorProcedure
	,@iErrorLine = @ErrorLine
	,@szErrorMessage = @ErrorMessage
	,@szParameters = ''
	,@iInserted = 0

	select @iRetCode = -1 

	select @szMsgTier1=@ErrorMessage,
		@szMsgTier2=u.MsgTier2,
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode

RETURN -1
END CATCH

END
GO


if not exists (select 1 from dbo.tblUIMessage (nolock) where UIErrCode = 207)
insert into dbo.tblUIMessage (UIErrCode,MsgTier2,MsgTier3)
select 207,
	'Error happened in SQL SP, please contact with PPJ administrator.',
	'Error happened in SQL SP, please contact with PPJ administrator.'
go

if not exists (select 1 from dbo.tblMsgOutput (nolock) where SPName = 'uspGetUsersStoreList')
insert into dbo.tblMsgOutput (SPName,RetCode,MsgTier1,UIErrCode)
select 'uspGetUsersStoreList',-1, 'SQL update error',207
union all
select 'uspGetUsersStoreList',-2, 'Parameter @biUserID is NULL or zero',207
union all
select 'uspGetUsersStoreList',-3, 'User {0} not found in dbo.tblUser table',207
union all
select 'uspGetUsersStoreList',-4, 'StoreID {0} not found in tblStore table',207
go
